#!/usr/bin/env node

/**
 * 检查远程MySQL数据库中帮派相关表是否存在
 * 数据库配置：
 * - 主机: 43.143.253.188
 * - 端口: 3306
 * - 用户名: nextjs
 * - 密码: yEAiRE2jcxTyRBy5
 * - 数据库: nextjs
 */

const mysql = require('mysql2/promise');

class FactionTableChecker {
    constructor() {
        this.connection = null;
        this.dbConfig = {
            host: '43.143.253.188',
            port: 3306,
            username: 'nextjs',
            password: 'yEAiRE2jcxTyRBy5',
            database: 'nextjs'
        };
    }

    async connect() {
        try {
            console.log('🔌 连接到远程MySQL数据库...');
            console.log(`   主机: ${this.dbConfig.host}`);
            console.log(`   端口: ${this.dbConfig.port}`);
            console.log(`   数据库: ${this.dbConfig.database}`);

            this.connection = await mysql.createConnection({
                host: this.dbConfig.host,
                port: this.dbConfig.port,
                user: this.dbConfig.username,
                password: this.dbConfig.password,
                database: this.dbConfig.database,
                charset: 'utf8mb4'
            });

            console.log('✅ 数据库连接成功');
            return true;
        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            return false;
        }
    }

    async checkTableExists(tableName) {
        try {
            const [rows] = await this.connection.execute(
                `SELECT COUNT(*) as count FROM information_schema.tables 
                 WHERE table_schema = ? AND table_name = ?`,
                [this.dbConfig.database, tableName]
            );

            return rows[0].count > 0;
        } catch (error) {
            console.error(`检查表 ${tableName} 失败:`, error.message);
            return false;
        }
    }

    async getTableStructure(tableName) {
        try {
            const [rows] = await this.connection.execute(`DESCRIBE ${tableName}`);
            return rows;
        } catch (error) {
            console.error(`获取表 ${tableName} 结构失败:`, error.message);
            return null;
        }
    }

    async checkFactionTables() {
        console.log('\n📋 检查帮派相关表...');

        const factionTables = [
            'factions',
            'faction_members'
        ];

        const results = {};

        for (const tableName of factionTables) {
            console.log(`\n🔍 检查表: ${tableName}`);

            const exists = await this.checkTableExists(tableName);
            results[tableName] = { exists, structure: null };

            if (exists) {
                console.log(`✅ 表 ${tableName} 存在`);

                // 获取表结构
                const structure = await this.getTableStructure(tableName);
                if (structure) {
                    results[tableName].structure = structure;
                    console.log(`📊 表结构:`);
                    structure.forEach(column => {
                        console.log(`   ${column.Field} (${column.Type}) - ${column.Null === 'YES' ? '可空' : '非空'} - ${column.Key ? column.Key : ''} - ${column.Default ? `默认: ${column.Default}` : ''}`);
                    });
                }
            } else {
                console.log(`❌ 表 ${tableName} 不存在`);
            }
        }

        return results;
    }

    async createFactionTables() {
        console.log('\n🏗️ 创建帮派相关表...');

        try {
            // 创建帮派表
            const createFactionsTable = `
                CREATE TABLE IF NOT EXISTS \`factions\` (
                    \`id\` bigint NOT NULL AUTO_INCREMENT COMMENT '帮派ID',
                    \`name\` varchar(64) NOT NULL COMMENT '帮派名称',
                    \`description\` text COMMENT '帮派描述',
                    \`level\` int NOT NULL DEFAULT '1' COMMENT '帮派等级',
                    \`exp\` bigint NOT NULL DEFAULT '0' COMMENT '帮派经验',
                    \`funds\` bigint NOT NULL DEFAULT '0' COMMENT '帮派资金',
                    \`memberCount\` int NOT NULL DEFAULT '0' COMMENT '成员数量',
                    \`maxMembers\` int NOT NULL DEFAULT '50' COMMENT '最大成员数',
                    \`leaderId\` bigint NOT NULL COMMENT '帮主ID',
                    \`announcement\` text COMMENT '帮派公告',
                    \`type\` int DEFAULT '0' COMMENT '帮派类型(1:派,2:教,3:会,4:帮,5:楼,6:阁,7:军,8:组,9:社,10:族)',
                    \`minLevel\` int DEFAULT '0' COMMENT '最低等级要求',
                    \`resistance\` varchar(255) DEFAULT NULL COMMENT '帮派抗性',
                    \`status\` int DEFAULT '1' COMMENT '状态(1:正常,0:解散)',
                    \`created_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    \`updated_at\` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                    PRIMARY KEY (\`id\`),
                    UNIQUE KEY \`uk_name\` (\`name\`),
                    KEY \`idx_leaderId\` (\`leaderId\`),
                    KEY \`idx_level\` (\`level\`),
                    KEY \`idx_status\` (\`status\`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='帮派表';
            `;

            await this.connection.execute(createFactionsTable);
            console.log('✅ 帮派表 (factions) 创建成功');

            // 创建帮派成员表
            const createFactionMembersTable = `
                CREATE TABLE IF NOT EXISTS \`faction_members\` (
                    \`id\` bigint NOT NULL AUTO_INCREMENT COMMENT '成员ID',
                    \`factionId\` bigint NOT NULL COMMENT '帮派ID',
                    \`characterId\` bigint NOT NULL COMMENT '角色ID',
                    \`role\` int DEFAULT '2' COMMENT '成员角色(0:不是成员,1:准成员,2:成员,3:管理,4:帮主)',
                    \`contribution\` bigint DEFAULT '0' COMMENT '贡献度',
                    \`joinDate\` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
                    \`lastActive\` timestamp NULL DEFAULT NULL COMMENT '最后活跃时间',
                    \`status\` tinyint DEFAULT '1' COMMENT '状态(1:正常,0:离线)',
                    \`created_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    \`updated_at\` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                    PRIMARY KEY (\`id\`),
                    UNIQUE KEY \`uk_factionId_characterId\` (\`factionId\`, \`characterId\`),
                    KEY \`idx_factionId\` (\`factionId\`),
                    KEY \`idx_characterId\` (\`characterId\`),
                    KEY \`idx_role\` (\`role\`),
                    KEY \`idx_contribution\` (\`contribution\`),
                    KEY \`idx_status\` (\`status\`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='帮派成员表';
            `;

            await this.connection.execute(createFactionMembersTable);
            console.log('✅ 帮派成员表 (faction_members) 创建成功');

            return true;
        } catch (error) {
            console.error('❌ 创建帮派表失败:', error.message);
            return false;
        }
    }

    async checkExistingData() {
        console.log('\n📊 检查现有数据...');

        try {
            // 检查帮派表数据
            const [factionCount] = await this.connection.execute('SELECT COUNT(*) as count FROM factions');
            console.log(`📈 帮派表记录数: ${factionCount[0].count}`);

            // 检查帮派成员表数据
            const [memberCount] = await this.connection.execute('SELECT COUNT(*) as count FROM faction_members');
            console.log(`👥 帮派成员表记录数: ${memberCount[0].count}`);

            // 如果有数据，显示一些示例
            if (factionCount[0].count > 0) {
                const [factions] = await this.connection.execute('SELECT id, name, level, memberCount FROM factions LIMIT 5');
                console.log('\n📋 帮派示例数据:');
                factions.forEach(faction => {
                    console.log(`   ID: ${faction.id}, 名称: ${faction.name}, 等级: ${faction.level}, 成员数: ${faction.memberCount}`);
                });
            }

            if (memberCount[0].count > 0) {
                const [members] = await this.connection.execute('SELECT id, factionId, characterId, role FROM faction_members LIMIT 5');
                console.log('\n👥 帮派成员示例数据:');
                members.forEach(member => {
                    console.log(`   ID: ${member.id}, 帮派ID: ${member.factionId}, 角色ID: ${member.characterId}, 角色: ${member.role}`);
                });
            }

        } catch (error) {
            console.error('❌ 检查现有数据失败:', error.message);
        }
    }

    async disconnect() {
        if (this.connection) {
            await this.connection.end();
            console.log('\n🔌 数据库连接已断开');
        }
    }

    async runCheck() {
        try {
            const connected = await this.connect();
            if (!connected) {
                return;
            }

            // 检查表是否存在
            const tableResults = await this.checkFactionTables();

            // 如果表不存在，询问是否创建
            const missingTables = Object.keys(tableResults).filter(table => !tableResults[table].exists);

            if (missingTables.length > 0) {
                console.log(`\n⚠️ 发现缺失的表: ${missingTables.join(', ')}`);
                console.log('🔧 正在创建缺失的表...');

                const created = await this.createFactionTables();
                if (created) {
                    console.log('✅ 所有帮派表创建完成');

                    // 重新检查表结构
                    console.log('\n🔄 重新检查表结构...');
                    await this.checkFactionTables();
                }
            } else {
                console.log('\n✅ 所有帮派表都已存在');
            }

            // 检查现有数据
            await this.checkExistingData();

            console.log('\n🎉 帮派表检查完成！');

        } catch (error) {
            console.error('❌ 检查过程中发生错误:', error.message);
        } finally {
            await this.disconnect();
        }
    }
}

// 运行检查
if (require.main === module) {
    const checker = new FactionTableChecker();
    checker.runCheck().catch(console.error);
}

module.exports = FactionTableChecker;
